# -*- coding: utf-8 -*-
import os
import time,json
from adminApp import create_backgroud_app, db, redis_store
from adminApp.models import Machine, AqyBandWidth95, TaskLog,WarnLogs, HardDisk, DefaultNicFlowData as dnf, BandWidthLog,Idc,SourceZabbixSnmpLog, SourceMySnmpLog, SourceAqySnmpLog
from datetime import datetime, timedelta, date
from sqlalchemy import func
from dateutil.relativedelta import relativedelta
from sqlalchemy import or_


if __name__ == "__main__":
    app=create_backgroud_app(os.getenv('FLASK_ENV', 'default'))
    not_finds = []
    success_rs = []

    with app.app_context():

        # db.session.commit()
        # 重新导入hahy01 mid=177  749B24B67072E207  插入爱奇艺数据
        # with open('/www/wwwroot/monserver/logs/snmplogs/749B24B67072E207.json') as f:
        #     datas = json.load(f)
        #     #
        #     # 插入爱奇艺
        #     aqy_inserts =[]
        #     for l in datas.get('aqysnmp'):
        #         # db.session.execute('insert into source_aqy_snmp_log(Unix,BytesSent,OutBytes,machine_id) values("%s","%s","%s","%s")' % (l[0].replace('_',' '),l[1],l[2],mid))
        #         aqy_inserts.append(dict(Unix=datetime.strptime(l[0], '%Y-%m-%d_%H:%M:%S'),
        #                                 BytesSent=l[1], OutBytes=l[2], machine_id=177))
        #     db.session.bulk_insert_mappings(SourceAqySnmpLog,aqy_inserts)
        #     db.session.commit()

        # 导入全部 清空表
        # db.session.execute('TRUNCATE TABLE `source_aqy_snmp_log`')
        # db.session.execute('TRUNCATE TABLE `source_my_snmp_log`')
        #
        # isum = 0
        # for root, dirs, files in os.walk('/www/wwwroot/monserver/logs/snmplogs'):
        #     for f in files:
        #         print('开始导入：{}'.format(os.path.join(root, f)))
        #         uuidOrDeviceid=f.split('.')[0]
        #         # print(uuidOrDeviceid)
        #         # 查询对应的ID 查询machine
        #         mid = db.session.query(Machine.id).filter(or_(Machine.system_uuid==uuidOrDeviceid, Machine.aqy_device_id==uuidOrDeviceid)).one_or_none()
        #         not_finds.append(uuidOrDeviceid)
        #         if mid:
        #             mid = mid[0]
        #             # 加载json 文件 插入数据库
        #             with open(os.path.join(root, f)) as f:
        #                 datas = json.load(f)
        #                 #
        #                 # 插入爱奇艺
        #                 # aqy_inserts =[]
        #                 # for l in datas.get('aqysnmp'):
        #                 #     # db.session.execute('insert into source_aqy_snmp_log(Unix,BytesSent,OutBytes,machine_id) values("%s","%s","%s","%s")' % (l[0].replace('_',' '),l[1],l[2],mid))
        #                 #     aqy_inserts.append(dict(Unix=datetime.strptime(l[0], '%Y-%m-%d_%H:%M:%S'),
        #                 #                             BytesSent=l[1], OutBytes=l[2], machine_id=mid))
        #                 # db.session.bulk_insert_mappings(SourceAqySnmpLog,aqy_inserts)
        #                 # db.session.commit()
        #                 # 插入自己的数据
        #                 my_inserts = []
        #                 for l in datas.get('mysnmp'):
        #                     my_inserts.append(dict(Unix=datetime.strptime(l[0], '%Y-%m-%d_%H:%M:%S'),
        #                                            BytesReceive=l[1], OutBytes=l[2],  machine_id=mid))
        #                 db.session.bulk_insert_mappings(SourceMySnmpLog,my_inserts)
        #                 db.session.commit()
        #                 success_rs.append(uuidOrDeviceid)
        #
        # print('未导入的文件')
        # print(not_finds)
        # with open('notputj.json', 'w') as f:
        #     json.dump(not_finds,f)

        # 部分缺少的，以及数据不全的，导入以前的备份文件
        fids = ['1508A14CC7C0069D','F07BC4B8A40BC601','37DA0C38D5021D71','9697479A47ED6058','EB340301FA4D5DD1','C9F0A4E402F59D60','0F6C073CE1DCAD3C','54D612D32027E8E0','A1E6A49A047C6F14','09136C909497B663','0A0DE8609444B144','5A66ECCE530E7871','E52FE8BD1B280A53','096334FBD74B75A7','E968B9D4145AE5FF','4858A3981CE458B9','ED08974F20610F0B','E37FBC43CB9A105F','067F35C01067DE3B','40332159BC73C4A2','B70747B605F31CDE','4FF01306900C1673','36C43EE737B3E771','95021E6E43BC0C92','F0CF66FCAE3CE6CB','454145BD080C0AA7','F20102D360F4042A','E803150D5E44A0ED','A2A313EFB1316826','75A5D8F2712FAC03','917CF790C35A9D9F','217C67DB982F0BC7','1A29417B910EE9B5','C4E100F668408026','7704F263CADF0F0D','059EDF69427A2FF3','3E28D7CB0362229C','6AA65808047F6462','080BC42059CF9A92','EA0CBF46C23BCBAE','34AB68D7C5E4C8D6','DF1C7EEC4718FE96','0B594535485E9007','B79D00FE1DDF3692']
        # print(len(fids))
        # for i in fids:
        #     mid = db.session.query(Machine.id).filter(
        #         or_(Machine.system_uuid == i, Machine.aqy_device_id == i)).one_or_none()
        #     if mid:
        #         mid = mid[0]
        #         print('开始导入：{}'.format('{}.json'.format(i)))
        #         with open('/www/wwwroot/monserver/logs/snmplogs2/{}.json'.format(i)) as f:
        #             # 插入爱奇艺
        #             aqy_inserts =[]
        #             for l in json.load(f):
        #                 # db.session.execute('insert into source_aqy_snmp_log(Unix,BytesSent,OutBytes,machine_id) values("%s","%s","%s","%s")' % (l[0].replace('_',' '),l[1],l[2],mid))
        #                 aqy_inserts.append(dict(Unix=datetime.strptime(l[0], '%Y-%m-%d_%H:%M:%S'),
        #                                         BytesSent=l[1], OutBytes=l[2], machine_id=mid))
        #             db.session.bulk_insert_mappings(SourceAqySnmpLog,aqy_inserts)
        #             db.session.commit()
        # 江苏盐城 sfc
        # gyctycids= ['0C64270590676208','AE0861DD593306E2','0FA6005E92270F96','ED936F3BE4EDCBC7','2A72FD56A99BA39A','46C30EC1D51E4EF5','DC037A3CE7179B6A','E32E594091769008']
        gyctycids = ['4c4c4544-0043-4d10-805a-b4c04f323532', '4c4c4544-004c-5810-8048-c8c04f303032',
                     '4c4c4544-0056-5010-804b-b9c04f593032', '4c4c4544-0057-4d10-8051-c4c04f483432',
                     '4c4c4544-0038-3210-8038-b4c04f323332', '4c4c4544-0053-3910-8051-c6c04f343232',
                     '4c4c4544-0053-3510-805a-c4c04f343232', '4c4c4544-0034-4d10-8039-c4c04f373432']
        for i in gyctycids:
            mid = db.session.query(Machine.id).filter(
                or_(Machine.system_uuid == i, Machine.aqy_device_id == i)).one_or_none()
            if mid:
                mid = mid[0]
                print('开始导入：{}'.format('{}.json'.format(i)))
                if os.path.isfile('/www/wwwroot/monserver/logs/snmplogs/{}.json'.format(i)):
                    with open('/www/wwwroot/monserver/logs/snmplogs/{}.json'.format(i)) as f:
                        # 插入爱奇艺
                        aqy_inserts =[]
                        for l in json.load(f).get('aqysnmp'):
                            # db.session.execute('insert into source_aqy_snmp_log(Unix,BytesSent,OutBytes,machine_id) values("%s","%s","%s","%s")' % (l[0].replace('_',' '),l[1],l[2],mid))
                            aqy_inserts.append(dict(Unix=l[0],
                                                    Unix_time=datetime.strptime(l[0], '%Y-%m-%d_%H:%M:%S'),
                                                    MbpsReceived=0, MbpsSent=int(l[2])/1024/1024, machine_id=mid))

                        db.session.bulk_insert_mappings(AqyBandWidth95,aqy_inserts)
                        db.session.commit()
                else:
                    print('文件：{} 不存在'.format('{}.json'.format(i)))

# 没有导入的文件
# ['FA7A035A6A4FF065', '39BF55E1081C0561', '44091A53B80DF9D4', '1B467A1218346C03', '0F21F230C9053C96', 'ACD46E5CF2F5515A', '3410EA931F013D90', '917CF790C35A9D9F', '1434396C2B9E92C0', '3AB471FA3FD2D7FC', '7A2706B2460B7514', 'FAAEB0C3482F9EC4', '059EDF69427A2FF3', 'B33F7B733162940F', '795773D80D13BC4F', '96F3A09F9F15F21B', '57A437F95018FFFA', '6970D89398B8F6FB', '749B24B67072E207', 'BC72EC079CA16FF6', '75F2797CB62369A3', 'F67E96794CE0C4B8', 'FAF194CB3E9D9110', 'C714BC1803295775', 'C2C20301991A74BC', 'AF3F0EC9049E7E62', '926E0FEB35A65B20', '3A081DEE92E42C04', '4c4c4544-0042-4810-8051-c8c04f325931', '4c4c4544-0042-3210-804e-c6c04f325931', '7D2F0CE55E7AFE3F', '13073B7CF7A1AAA9', '36C43EE737B3E771', '09BC70E17507EA9F', '34AB68D7C5E4C8D6', '95021E6E43BC0C92', '2a974d56-ce7d-d42e-8935-3cff676872e0', '4c4c4544-0039-4d10-804e-c8c04f325931', '3FA30EB7D1096496', '4BED6ABBA03192AD', '41674E74E3240979', '0B594535485E9007', 'F26269C29C697909', '05EA445FC26400EE', 'F09B3D704696997C', '454E5F027576BFFE', 'DF63C018145D0096', 'F0CF66FCAE3CE6CB', '2B65B3BCD40D0623', 'FF61050F6212A515', '4FF01306900C1673', '5D9C7E079C1E0851', '3E28D7CB0362229C', '4B06E0A44F03FDCC', 'B741043905E1BE73', '59579AE9096D0675', '476F425442D37A3A', 'DAD00556D9630665', '0E664BBDEE1DD62C', '1C4744E8E86D3DAA', '6CDFC50A08349356', '4c4c4544-0048-5010-8046-b1c04f303032', '576E63B4B3466A16', 'B4095B75359A95DC', '0DC55137CF052B23', 'B2E992C940E82B1D', 'D44E8000A8AC971C', '1477D0D1096AE941', '6d3d4d56-c16d-c0b2-a397-e9ef13f58f6f', '5707D6210956576C', '07950266A22105DF', '0312EA765C9FCE68', '942F00D6F75B6700', '4c4c4544-0031-4b10-8037-b8c04f473032', '3F0126E40E4AE12A', '4548D1B3B968A63A', '2235943F5A0A7DC3', 'BF41A34DE4E1E10A', '0BFE28DF2823C5C2', 'DF4D7E1D7D97614F', 'E803150D5E44A0ED', '4C46422ACE69E107', 'DD787B19190396DC', '6AA65808047F6462', '02594808ED2705C1', '6FE50B6A9E3E930C', '674D66F761592638', '69bab174-6a28-48c7-8380-c466203b73e5', '4c4c4544-0043-4d10-805a-b4c04f323532', '54F29A1C68F5D3CF', 'E6A5B4FC37C7DB4F', '40332159BC73C4A2', 'B00AB6F53892B00C', 'A2A313EFB1316826', '00BFCD316409D097', 'A5534107BFEF5029', 'BC4FE2D40E4C4371', 'CA69930E79D5BAEC', '5666BE5768F7059E', '03466DA2123E5ADE', 'A91637521328C073', '6e3c4d56-d730-b6a3-1e72-e61c99585db7', 'FCBC6529C89C5C4A', '4c4c4544-0030-3610-804a-b5c04f355a31', '7BEFDD96D864EDC2', 'D344F7F81AA73205', '78715D657F45532F', '6bd34d56-b1ae-66d7-f642-b9496a49621b', '1E470A92EF06DEC1', 'CE9EE902230B46CF', '47955624A23905CE', '0ED4D03BEF2D0504', '7704F263CADF0F0D', '21B5F741AA3734CB', '0CB557206F284920', '9743FD965AED4A97', '382BD97627CB35F6', 'DBF0B03BA815F37B', '080BC42059CF9A92', '4A5F48D8A11B6E18', 'C0C16C01BCE976D6', 'A9150BFE57EF400E', '776E301DAD645F91', '5953A4957DA2C1D9', 'B4E2FEAEF976390C', 'F70C553D569B4E93', '7E453B743277E43F', '0B6E3475EEB67C4E', '982DA0D14F3DDD96', '980477EE354FC4A1', 'D6DB17ED4645E1FE', '05051167099152F4', 'D90B3603E303CFF1', 'A6B1BBB21B7ABBDD', '1C155DD977B6750A', 'A4C5ED06500A99F3', 'B79CBB7FD57DB51B', 'BDE9456EBF3762CD', '921F7C104CF25424', 'D5D49D290E183466', 'DAC4F2B8C15CEF03', '7A429D314F8002FE', 'F84E30D227D73F43', 'C495572B3B916E3D', 'FF98012590A047B4', '35A5970E34E79DCA', '11214C6B2EEC19FA', '0707D9570D781078', '9C7D6E47071D5F06', '15DCD75891BFD80A', '32479246E2096476', 'f69473f5-c3df-49db-942c-9a774351f6b2', '4c4c4544-004e-5410-8034-b4c04f425831', 'E9BB7512CA3274D1', '2ADA000295EBE30A', '6F2F052F2906F16E', 'B64E995C7207CC08', '7433094BD502ADA2', '00F473C812BA9509', 'E7D1313F999077B1', '749804E554189948', 'BECA072807DC1644', '1B4B031C971FD9BE', 'FB33D748E5D44F92', 'daae76ce-3afe-4fab-bafe-1586fb4c29ae', '1D6F071F16CF1858', '057CB97EF733C91C', '95D0BE350A6BE85E', 'F8E13C4E2967A0BB', '60cfda5f-3b48-470c-a51f-742c76328342', 'e8a69ed0-12f0-468b-907b-9d74e0bd80c7', '7fd14d56-bcc6-4701-aa80-52ffc6d41707', 'CB509BE2D14A57EA', '6E7D53B1F3915E28', '1417599624071D08', '42B4B4E4C7FA7A73', 'DEC75D3465A020A3', 'D2CAA89440382E55', 'DF1C7EEC4718FE96', '0AF377F9CE01E4AC', 'CC0F5D29BB9060D9', '7C5E5E38E437D5CF', '45A0C3A807AAFF73', 'FCF49E0657F62995', '4c4c4544-0056-5010-804b-b9c04f593032', '06CFFBDD092E03A4', '21384F726EC7FCDB', '6D44B9560008A933', 'D8E99409E9BFE20A', '31A83466D2A6D22A', 'D8B5FA340EFAA7A5', 'C4E100F668408026', '27D4085CA3327A54', '9B95741029C32B08', '454145BD080C0AA7', '4BD09D124705F7C2', '170AAC46F0454AFA', '26EF9BF4B6BD0340', '4c4c4544-0043-3510-8036-c3c04f443032', '39AD081AB0EE4159', '57B42C1C21E20E53', '4c4c4544-0033-4d10-8032-b4c04f395831', 'FD426EF8020321B5', 'B2DB2E279DEB35BD', '959551E5703C78D8', '4722DE580D7DB82F', '4c4c4544-0042-4210-8052-b6c04f573232', 'B70747B605F31CDE', '9CDF1406B7100F2F', 'F20102D360F4042A', '366E40A8A680AEDE', 'C1E3A9FC48D06ABF', 'BA2B4D2DCE7E2C79', '4c4c4544-0035-5610-8051-c3c04f483432', 'A046F92E26314328', '94EC7A0C09E2E32F', 'CA90ED5BF9FC029C', '4c4c4544-004c-5810-8048-c8c04f303032', '6F1AF314C3026CF7', 'BE3601A9DEEB0E0E', 'E0530D0779B4984E', '3E32A12D94E756C0', '746D0192290F7A1C', '052401DB99347E3B', 'BC4F055EEB6979D6', '21F5E9A05A06FBDC', '3C94F4019E3A5C77', 'AACDFD54E9060D1D', 'C6412E4B08BEAE5C', '940C66B3A4039460', '1A29417B910EE9B5', '38B056A173481D29', '2B0BED94F534AC34', '4c4c4544-0046-3810-804d-c6c04f463358', '307CDD95A8FCEA22', '29C5155CA09F73B9', '680ABE777A54EDD9', '2252F93C4C39C45F', '7A9B05C1B9C5A4C0', 'B975CDDEBF9FB016', 'CD42E69C0197BCF1', 'B79D00FE1DDF3692', '9c16bb8b-62a4-4897-a896-d31b829c5bb3', 'AEA5AC0F01A59F76', 'A2173B6D011645D2', 'C34F3D58627D04AC', 'AC41E6DEA22D083D', '4c4c4544-0048-4610-8056-c3c04f343232', 'd7a634ae-eea8-4ebd-a1b4-d2a6d55a9b6c', 'DE63ABAA9CE12BB6', '217C67DB982F0BC7', '3E0D4DA2093BCED2', '3D06169857079055', 'E8DEF615097BAA25', 'C65E930BD4079568', '2D3509DDCB71966F', 'AC073FC11DBCD253', '92047DE80B6D6B16', '5e3a4d56-38d4-d388-72a5-f71ebe80fdb8', '75A5D8F2712FAC03', 'EA0CBF46C23BCBAE', '755A72FA6350D3EA', '75417E4366256441', '2DE0C3B9EB1CA2A8', 'A0BCD14D960DB30F', 'A8DBCE4D2C3EFF6A', 'F53763C92D4BACBF', 'F5ABACC00B447336', '2953A6994AA712F5', '344D46E79412FE30', '00F2F794492DFC33', '09EB746C6EEBC3B1', '470B091C222F5EC4', '7B310A2AB6B3EDC3', '6BDBBFFB1FD7B444', '1E5275400ECFB41C', '4FB00EAF0123DFBC', 'A41AE917DBDF4DE9', '644E34E0AF177530', '6D80DFD1ACA91F39', 'BCA5DB020DFC2D92']